# 项目工时信息：
# 月份项目工时：monthProjectWorktime {"$yearMonth" : {"$projectName" : {"$staffName" : "$worktime"}}}

import openpyxl
import os
import re

class WorktimeModel(object):
    projectNames = {}
    projectWorkTime = {}
    myProjectWorkTime = {}
    monthProjectWorktime = {}
    myMonthProjectWorktime = {}
    projectWorkInit = {}

# 数据初始化
def DataInit():
    projectWorkInit = {'招商平安资产基金管理子系统':'10.12',
                       '服务商管理子系统项目':'10.7',
                       '招银国际私募基金管理系统建设项目':'13.2',
                       '青岛国信基金管理系统建设项目':'2.26'}
    return projectWorkInit

# 获取所有项目名称 项目信息：projectInfo {projectName : "", projectDepartment : ""}
def getProjectInfo(dir):
    projectInfo = {}
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                yearMonth = getYearMonth(filename)
                inout = getFileType(filename)
                # 外包
                if inout == '_外勤':
                    projectNameIndex = 11
                    projectDeptIndex = 10
                else:
                    # 内勤
                    projectNameIndex = 5
                    projectDeptIndex = 7
                    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                        projectName = row[projectNameIndex].value
                        projectDept = row[projectDeptIndex].value
                        if projectName not in projectInfo.keys():
                            projectInfo[projectName] = {'dept': projectDept}
    return projectInfo

# 获取指定部门的项目列表
def getProjectNamesByDept(dept, projectInfo):
    projectNames = []
    for k in projectInfo.keys():
        if dept == projectInfo[k]['dept']:
            projectNames.append(k)
    return projectNames

## 月份｜项目｜员工｜工时 monthProjectStaffWorktime {"$yearMonth" : {"$projectName" : {"$staffName" : "$workTime"}}}
def getProjectWorktimeDetailByMonth(dir):
    projectWorktimeDetailByMonth = {}
    djProjectInfo = getProjectNamesByDept('多金系统开发部', getProjectInfo(dir))
    print(djProjectInfo)
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                yearMonth = getYearMonth(filename)
                # 外包
                if getFileType(filename) == '_外勤':
                    projectNameIndex = 11
                    staffNameIndex = 1
                    workTimeIndex = 14
                else:
                    # 内勤
                    projectNameIndex = 5
                    staffNameIndex = 1
                    workTimeIndex = 10
                for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
                    projectName = row[projectNameIndex].value
                    worktime = row[workTimeIndex].value
                    staffName = row[staffNameIndex].value
                    if projectName in djProjectInfo:
                        if yearMonth not in projectWorktimeDetailByMonth.keys():
                            projectWorktimeDetailByMonth[yearMonth] = {projectName : {staffName : worktime}}
                        elif projectName not in projectWorktimeDetailByMonth[yearMonth].keys():
                            projectWorktimeDetailByMonth[yearMonth][projectName] = {staffName : worktime}
                        elif staffName not in projectWorktimeDetailByMonth[yearMonth][projectName].keys():
                            projectWorktimeDetailByMonth[yearMonth][projectName][staffName] = worktime
                        else:
                            projectWorktimeDetailByMonth[yearMonth][projectName][staffName] = round((float(worktime) + float(projectWorktimeDetailByMonth[yearMonth][projectName][staffName])), 2)
    return projectWorktimeDetailByMonth


# 输出结果
def genWorktimeDetails(projectWorkInit, projectWorktimeDetailByMonth):
    djProjectInfo = getProjectNamesByDept('多金系统开发部', getProjectInfo(dir))
    sheetTitle = [''] + djProjectInfo
    print(sheetTitle)
    staffNames = getStaffNames(projectWorktimeDetailByMonth)
    sheetData = []
    for row in sheetData:
        None
    #print(myProjectWorkTime)



# 获取人员名称
def getStaffNames(projectWorktimeDetailByMonth):
    staffNames = []
    for month in projectWorktimeDetailByMonth:
        projectWorktimeDetail = projectWorktimeDetailByMonth[month]
        for project in projectWorktimeDetail.keys():
            names = projectWorktimeDetail[project]
            for name in names.keys():
                if name not in staffNames:
                    staffNames.append(name)
    #print(staffNames)
    return staffNames


# 获取文件名的年月
def getYearMonth(filename):
    yearMonthRegex = re.compile(r'\d{4}年\d{2}月')
    mo = yearMonthRegex.search(filename)
    return mo.group()

# 获取文件名的内外包
def getFileType(filename):
    fileType = re.compile(r'_\w{2}')
    mo = fileType.search(filename)
    return mo.group()

if __name__ == '__main__':
    projectWorkInit = DataInit()
    dir = '/Users/zhangxiangbin/Desktop/工时统计/'
    projectWorktimeDetailByMonth = getProjectWorktimeDetailByMonth(dir)
    print(projectWorktimeDetailByMonth)
    #dataOutput(projectWorkInit, projectWorktimeDetailByMonth)
    getStaffNames(projectWorktimeDetailByMonth)